﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
    public partial class LINQ : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            LoadGrid();
        }

        private void LoadGrid()
        {
            using (NrthwndDataContext db = new NrthwndDataContext())
            {
                // LINQ ...... Language Integrated Query (C# or VB or J#)

                // Retrieve all categories
                // var customers = db.Customers.Where(c => c.Country == "Germany" && 
                //                                        c.ContactTitle.Contains("Representative"));

                // var customers = db.Customers.Select(c => new { c.CompanyName, c.ContactName, c.City });

                // SELECT CompnyName, ContactName, City FROM Customers

                var shippers = db.Shippers;

                // Display categories in the gridview
                GridView1.DataSource = shippers;
                GridView1.DataBind();
            }
        }

        protected void btnAdd_Click(object sender, EventArgs e)
        {
            Shipper s = new Shipper();
            s.CompanyName = txtNewShipperName.Text;
            s.Phone = txtNewPhone.Text;

            using (var db = new NrthwndDataContext())
            {
                db.Shippers.InsertOnSubmit(s);
                db.SubmitChanges();
            }

            LoadGrid();
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            using (var db = new NrthwndDataContext())
            {
                int shipperIDToBeDeleted = int.Parse(txtShipperID.Text);

                Shipper matchedShipper = db.Shippers.Where(s => s.ShipperID == shipperIDToBeDeleted).SingleOrDefault();
                if (matchedShipper == null)
                {
                    lblDelete.Text = "No shipper found.";
                }
                else
                {
                    db.Shippers.DeleteOnSubmit(matchedShipper);
                    db.SubmitChanges();
                }
            }

            LoadGrid();
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            int shipperIDToBeUpdated = int.Parse(txtShipperIDUpdate.Text);

            using (var db = new NrthwndDataContext())
            {
                Shipper matchedShipper = db.Shippers.Where(s => s.ShipperID == shipperIDToBeUpdated).SingleOrDefault();
                if (matchedShipper == null)
                {
                    lblUpdate.Text = "No shipper found.";
                }
                else
                {
                    matchedShipper.CompanyName = txtNewShipperName.Text;
                    matchedShipper.Phone = txtNewPhone.Text;

                    db.SubmitChanges();
                }
            }
            LoadGrid();
        }

        protected void btnModify_Click(object sender, EventArgs e)
        {
            int shipperIDToBeUpdated = int.Parse(txtShipperIDUpdate.Text);

            using (var db = new NrthwndDataContext())
            {
                Shipper matchedShipper = db.Shippers.Where(s => s.ShipperID == shipperIDToBeUpdated).SingleOrDefault();
                if (matchedShipper == null)
                {
                    lblUpdate.Text = "No shipper found.";
                }
                else
                {
                    txtNewShipperName.Text = matchedShipper.CompanyName;
                    txtNewPhone.Text = matchedShipper.Phone;
                }
            }
        }
    }
}